# -*- coding: utf-8 -*-
import csv
import sqlite3
import os

'''
Class to create the CSV files
@author: Edgar Pereira
Nº 6207
'''

class MyCSV():
    #-------------------------------------------------------
    #Class constructor
    #-------------------------------------------------------
    def __init__(self):
        pass    
        
    
    #-------------------------------------------------------   
    # Total nº of teachers in the higher education system per year
    #-------------------------------------------------------
    def total_prof_ano(self):
        print ('Total nº of teachers in the higher education system per year')
        path = 'CSV/TotProfsAnos.csv' # define the path to create the file
        try:
            os.stat(os.path.dirname(path))
            pass
        except:
            os.mkdir(os.path.dirname(path))
            pass
        ficheiro = open(path,"wb") # open file for writing
        file = csv.writer( ficheiro, delimiter=',') #choses the delimiter of the 'fields'
        header = ("TOTAL DE PROFESSORES DO ENSINO SUPERIOR POR ANO",)
        file.writerow(header)
        title = ('ANO', 'TOTAL')
        file.writerow(title) 
         
              
        for ano in range(0, 10): # runs one time per each year
            print ('Processing the 200{0} year'.format(ano))
            conn = sqlite3.connect('rebides200{0}.db'.format(ano))
            cursor = conn.cursor()
            try: # makes the search to the databese
                cmd = """SELECT COUNT (*) FROM docentes"""
                cursor.execute(cmd)
                r = cursor.fetchone() # fetch one line
            except:
                print ('ERROR')
            pass
            data = ('200{0}'.format(ano), r[0]) # pass the the data: year, nº of teachers
            file.writerow(data) # write the data on the file
        ficheiro.close() # closes the file
    pass
    
    
    #-------------------------------------------------------
    # Total nº of teachers per institution and per year
    #-------------------------------------------------------
    def total_prof_est_ano(self):
        print ('Total nº of teachers per institution and per year')
        path = 'CSV/TotProfEstAnos.csv' # define the path to create the file
        try:
            os.stat(os.path.dirname(path))
            pass
        except:
            os.mkdir(os.path.dirname(path))
            pass
        ficheiro = open(path,"wb") # open file for writing
        file = csv.writer( ficheiro, delimiter=',')
        header = ('10+ DO TOTAL DE PROFESSORES POR ESTABELECIMENTOS DE ENSINO SUPERIOR POR ANO',)
        file.writerow(header)
        separ = ('######################################',)
              
        for ano in range(0, 10): # runs one time per each year
            print ('Processing the 200{0} year'.format(ano))
            year = ('Ano de 200{0}'.format(ano),)
            file.writerow(separ)            
            file.writerow(year)
            file.writerow(separ)
            title = ('INSTITUIÇÃO', 'Nº PROFS')
            file.writerow(title)  
            conn = sqlite3.connect('rebides200{0}.db'.format(ano)) # connect to the desirable database
            cursor = conn.cursor()
            try: # makes the search to the databese
                cmd = """SELECT     estabelecimento,
                         COUNT(DISTINCT docente) AS Total_docentes
                         FROM       fichas_docencia
                         GROUP BY	estabelecimento
                         ORDER by   Total_docentes DESC
                         LIMIT 10"""
                cursor.execute(cmd)
                r = cursor.fetchall() # fetch all data
                for row in r:
                    data = ('{0}'.format(row[0].encode('utf-8')), row[1]) # pass the data: estabelecimento, nº professores
                    file.writerow(data) # writes the data on the file
            except:
                print ('ERROR')
            pass
                        
        ficheiro.close() # closes the file
    pass
    
    
    #-------------------------------------------------------
    # Total nº of teachers per degree and per year
    #-------------------------------------------------------
    def total_prof_grau_ano(self):
        print ('Total nº of teachers per degree and per year')
        path = 'CSV/TotProfGrauAnos.csv' # define the path to create the file
        try:
            os.stat(os.path.dirname(path))
            pass
        except:
            os.mkdir(os.path.dirname(path))
            pass
        ficheiro = open(path,"wb") # open file for writing
        file = csv.writer( ficheiro, delimiter=',')
        header = ('10+ DO TOTAL DE PROFESSORES POR GRAU E POR ANO',)
        file.writerow(header)
        separ = ('######################################',)
              
        for ano in range(0, 10):
            print ('Processing the 200{0} year'.format(ano))
            year = ('Ano de 200{0}'.format(ano),)
            file.writerow(separ)            
            file.writerow(year)
            file.writerow(separ)
            title = ('GRAU', 'Nº PROFS')
            file.writerow(title)  
            conn = sqlite3.connect('rebides200{0}.db'.format(ano)) # connect to the desirable database
            cursor = conn.cursor()
            try: # makes the search to the databese
                cmd = """SELECT     grau,
                         COUNT(DISTINCT docente) AS Total_docentes
                         FROM       fichas_docencia
                         GROUP BY	grau
                         ORDER by   Total_docentes DESC
                         LIMIT 10"""
                cursor.execute(cmd)
                r = cursor.fetchall()
                for row in r:
                    data = ('{0}'.format(row[0].encode('utf-8')), row[1])# pass the the data: grau, nº de professores
                    file.writerow(data) # writes the data on the file
            except:
                print ('ERROR')
            pass
                        
        ficheiro.close() # closes the file
    pass
    
    
    #-------------------------------------------------------
    # Total nº of teachers per institution per degree and per year
    #-------------------------------------------------------
    def total_prof_grau_est_ano(self):
        print ('Total nº of teachers per institution per degree and per year')
        path = 'CSV/TotProfEstGrauAnos.csv' # define the path to create the file
        try:
            os.stat(os.path.dirname(path))
            pass
        except:
            os.mkdir(os.path.dirname(path))
            pass
        ficheiro = open(path,"wb") # open file for writing
        file = csv.writer( ficheiro, delimiter=',')
        header = ('20+ DO TOTAL DE PROFESSORES POR ESTABELECIMENTO, GRAU E ANO',)
        file.writerow(header)
        separ = ('######################################',)
              
        for ano in range(0, 10):
            print ('Processing the 200{0} year'.format(ano))
            year = ('Ano de 200{0}'.format(ano),)
            file.writerow(separ)            
            file.writerow(year)
            file.writerow(separ)
            title = ('ESTABELECIMENTO', 'GRAU', 'Nº PROFS')
            file.writerow(title)  
            conn = sqlite3.connect('rebides200{0}.db'.format(ano)) # connect to the desirable database
            cursor = conn.cursor()
            try: # makes the search to the databese
                cmd = """SELECT estabelecimento, grau,
                         COUNT(DISTINCT docente) AS Total_docentes
                         FROM		fichas_docencia
                         GROUP BY	estabelecimento, grau
                         ORDER by   Total_docentes DESC
                         LIMIT 20"""
                cursor.execute(cmd)
                r = cursor.fetchall()
                for row in r:
                    data = ('{0}'.format(row[0].encode('utf-8')), '{0}'.format(row[1].encode('utf-8')), row[2]) # pass the the data: estabelecimento, grau, nº professores
                    file.writerow(data) # write the data on the file
            except:
                print ('ERROR')
            pass
                        
        ficheiro.close() # closes the file
    pass
    
    
    #-------------------------------------------------------
    # List of institutions/establishements per year
    #-------------------------------------------------------
    def list_est_ano(self):
        print ('List of institutions/establishements per year')
        path = 'CSV/LstEstAnos.csv' # define the path to create the file
        try:
            os.stat(os.path.dirname(path))
            pass
        except:
            os.mkdir(os.path.dirname(path))
            pass
        ficheiro = open(path,"wb") # open file for writing
        file = csv.writer( ficheiro, delimiter=',')
        header = ("LISTA DE ESTABELECIMENTOS POR ANO",)
        separ = ('######################################',)
        file.writerow(header)
        
        for ano in range(0, 10):
            print ('Processing the 200{0} year'.format(ano))
            conn = sqlite3.connect('rebides200{0}.db'.format(ano)) # connect to the desirable database
            cursor = conn.cursor()
            
            year = ('Ano de 200{0}'.format(ano),)
            file.writerow(separ)            
            file.writerow(year)
            file.writerow(separ)
            
            try: # makes the search to the databese 
                cmd = """SELECT designacao FROM estabelecimentos
                         ORDER BY designacao """
                cursor.execute(cmd)
                r = cursor.fetchall()
                i = 1
                for row in r:
                    data = (i, '{0}'.format(row[0].encode('utf-8')))# pass the the data: indice, nome da instituição
                    file.writerow(data) # writes the data on the file
                    i += 1  
            except:
                print ('ERROR')
            pass
        ficheiro.close() # closes the file
    pass    
    
    
    #-------------------------------------------------------
    # List of olders of a degree per year
    #-------------------------------------------------------
    def list_prof_grau_anos(self):
        print ('List of olders of a degree per year')
        path = 'CSV/LstPrGrAnos.csv' # define the path to create the file
        try:
            os.stat(os.path.dirname(path))
            pass
        except:
            os.mkdir(os.path.dirname(path))
            pass
        ficheiro = open(path,"wb") # open file for writing
        file = csv.writer( ficheiro, delimiter=',')
        header = ("LISTA DE PROFESSORES POR GRAU E ANO",)
        separ = ('######################################',)
        file.writerow(header)
        
        for ano in range(0, 10):
            print ('Processing the 200{0} year'.format(ano))
            conn = sqlite3.connect('rebides200{0}.db'.format(ano)) # connect to the desirable database
            cursor = conn.cursor()
            
            year = ('Ano de 200{0}'.format(ano),)
            file.writerow(separ)            
            file.writerow(year)
            file.writerow(separ)    
            try:# makes the search to the databese
                cmd = """SELECT docente, grau
                         FROM fichas_docencia
                         INNER JOIN docentes
                         ON fichas_docencia.docente = docentes.nome_completo
                         INNER JOIN graus
                         ON fichas_docencia.grau = graus.designacao
                         ORDER BY docente, grau"""
                cursor.execute(cmd)
                r = cursor.fetchall()
                i = 1
                for row in r:
                    data = ('{0}'.format(row[0].encode('utf-8')), '{0}'.format(row[1].encode('utf-8')))# pass the the data: nome prof, grau
                    file.writerow(data) # writes the data on the file
                    i += 1  
            except:
                print ('ERROR')
            pass
        ficheiro.close() # closes the file
    pass
    
    
    #-------------------------------------------------------
    # List of olders of a degree in one year
    #-------------------------------------------------------
    def list_prof_grau_ano(self, ano):
        print ('List of olders of a degree in the 2000{0} year'.format(ano))
        path = 'CSV/LstPrGrAno200{0}.csv'.format(ano)
        try:
            os.stat(os.path.dirname(path))
            pass
        except:
            os.mkdir(os.path.dirname(path))
            pass
        ficheiro = open(path,"wb") # open file for writing
        file = csv.writer( ficheiro, delimiter=',')
        header = ("LISTA DE PROFESSORES POR GRAU",)
        separ = ('######################################',)
        file.writerow(header)
        conn = sqlite3.connect('rebides200{0}.db'.format(ano)) # connect to the desirable database
        cursor = conn.cursor()
        year = ('Ano de 200{0}'.format(ano),)
        file.writerow(separ)            
        file.writerow(year)
        file.writerow(separ)
        
        try: # makes the search to the databese
            cmd = """SELECT docente, grau
                     FROM fichas_docencia
                     INNER JOIN docentes
                     ON fichas_docencia.docente = docentes.nome_completo
                     INNER JOIN graus
                     ON fichas_docencia.grau = graus.designacao
                     ORDER BY docente, grau"""
            cursor.execute(cmd)
            r = cursor.fetchall()
            i = 1
            for row in r:
                data = ('{0}'.format(row[0].encode('utf-8')), '{0}'.format(row[1].encode('utf-8')))# pass the the data: nome prof, grau
                file.writerow(data) # write the data on the file
                i += 1   
        except:
            print ('ERROR')
        pass
        ficheiro.close() # closes the file
    pass

##mcsv = MyCSV()
##mcsv.total_prof_ano()
##mcsv.total_prof_est_ano()
##mcsv.total_prof_grau_ano()
##mcsv.total_prof_grau_ano()
##mcsv.total_prof_grau_est_ano()
##mcsv.list_est_ano()
##mcsv.list_prof_grau_anos()
##mcsv.list_prof_grau_ano(0)